﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using FastFood.Business.Entities;
//using FastFood.Entidad.Tipos;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace FastFood.DataAccess
{
    public class PersonalDalc
    {
        Database _personalDB;
        private PersonalDalc() 
        {
            _personalDB = DatabaseFactory.CreateDatabase();
        }
        private readonly static PersonalDalc _Instancia = new PersonalDalc();
        public static PersonalDalc Instancia
        {
            get { return PersonalDalc._Instancia; }
        }

        public bool agregarPersonal(Personal personal)
        {
            using (SqlConnection conexion = new SqlConnection(Conexion.Instancia.CadenaConexion()))
            using (SqlCommand comando = new SqlCommand("dbo.[proc_PersonalInsert]",conexion))
            {
                try
                {
                    comando.CommandType = CommandType.StoredProcedure;
                    conexion.Open();

                    comando.Parameters.Add("@personalID", SqlDbType.Int).Direction = ParameterDirection.Output;
                    comando.Parameters.Add("@DNI", SqlDbType.NChar, 8).Value = personal.DNI;
                    comando.Parameters.Add("@nombres", SqlDbType.NVarChar, 50).Value = personal.nombres;
                    comando.Parameters.Add("@apellidos", SqlDbType.NVarChar, 50).Value = personal.apellidos;
                    comando.Parameters.Add("@direccion", SqlDbType.NVarChar, 30).Value = personal.direccion;
                    comando.Parameters.Add("@telefono", SqlDbType.NVarChar, 20).Value = personal.telefono;
                    comando.Parameters.Add("@eMail", SqlDbType.NVarChar, 50).Value = personal.eMail;
                    
                    if (comando.ExecuteNonQuery() == 0)
                    {
                        return false;
                    }
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }

        public bool modificarPersonal(Personal personal)
        {
            using (SqlConnection conexion = new SqlConnection(Conexion.Instancia.CadenaConexion()))
            using (SqlCommand comando = new SqlCommand("dbo.[proc_PersonalUpdate]",conexion))
            {
                try
                {
                    comando.CommandType = CommandType.StoredProcedure;
                    conexion.Open();

                    comando.Parameters.Add("@personalID", SqlDbType.Int).Value = personal.personalID;
                    comando.Parameters.Add("@DNI", SqlDbType.NChar, 8).Value = personal.DNI;
                    comando.Parameters.Add("@nombres", SqlDbType.NVarChar, 50).Value = personal.nombres;
                    comando.Parameters.Add("@apellidos", SqlDbType.NVarChar, 50).Value = personal.apellidos;
                    comando.Parameters.Add("@direccion", SqlDbType.NVarChar, 30).Value = personal.direccion;
                    comando.Parameters.Add("@telefono", SqlDbType.NVarChar, 20).Value = personal.telefono;
                    comando.Parameters.Add("@eMail", SqlDbType.NVarChar, 50).Value = personal.eMail;
                    
                    if (comando.ExecuteNonQuery() == 0)
                    {
                        return false;
                    }
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }

        public bool eliminarPersonal(short id)
        {
            using (SqlConnection conexion = new SqlConnection(Conexion.Instancia.CadenaConexion()))
            using (SqlCommand comando = new SqlCommand("dbo.[proc_PersonalDelete]", conexion))
            {
                try
                {
                    comando.CommandType = CommandType.StoredProcedure;
                    conexion.Open();
                    comando.Parameters.Add("@personalID", SqlDbType.Int).Value = id;
                    
                    if (comando.ExecuteNonQuery() == 0)
                    {
                        return false;
                    }
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }

        }

        public Personal obtenerPersonalPorID(short id)
        {
            Personal c = null;
            using (SqlConnection conexion = new SqlConnection(Conexion.Instancia.CadenaConexion()))
            using (SqlCommand comando = new SqlCommand("dbo.[proc_PersonalLoadByPrimaryKey]",conexion))
            {
                try
                {
                    comando.CommandType = CommandType.StoredProcedure;
                    conexion.Open();
                    comando.Parameters.Add("@personalID", SqlDbType.Int).Value = id;
                    
                    SqlDataReader lector = comando.ExecuteReader();
                    if (lector.Read())
                    {
                        c = new Personal();
                        c.personalID = lector.GetInt16(0);
                        c.DNI = lector.GetString(1);
                        c.nombres = lector.GetString(2);
                        c.apellidos = lector.GetString(3);
                        c.direccion = lector.GetString(4);
                        c.telefono = lector.IsDBNull(5)? null :lector.GetString(5);
                        c.eMail = lector.IsDBNull(6) ? null : lector.GetString(6);
                    }
                }
                catch (Exception)
                {
                    return null;
                }
            }
            return c;

        }

        public List<Personal> listarPersonal()
        {
            List<Personal> lista = new List<Personal>();
            Personal p = null;
            using (SqlConnection conexion = new SqlConnection(Conexion.Instancia.CadenaConexion()))
            using (SqlCommand comando = new SqlCommand("[proc_PersonalLoadAll]", conexion))
            {
                try
                {
                    conexion.Open();
                    SqlDataReader lector = comando.ExecuteReader();
                    while (lector.Read())
                    {
                        p = new Personal();
                        p.personalID = lector.GetInt16(0);
                        p.DNI = lector.GetString(1);
                        p.nombres = lector.GetString(2);
                        p.apellidos = lector.GetString(3);
                        p.direccion = lector.GetString(4);
                        p.telefono = lector.IsDBNull(5) ? null : lector.GetString(5);
                        p.eMail = lector.IsDBNull(6) ? null : lector.GetString(6);
                        lista.Add(p);
                    }
                    lector.Close();

                }
                catch (Exception)
                {
                    return null;
                }
            }
            return lista;
        }

        public List<Personal> listarPersonalByDNI(String dni)
        {
            List<Personal> lista = new List<Personal>();
            Personal p = null;
            using (SqlConnection conexion = new SqlConnection(Conexion.Instancia.CadenaConexion()))
            using (SqlCommand comando = new SqlCommand("[proc_PersonalByDNI]", conexion))
            {
                try
                {
                    comando.CommandType = CommandType.StoredProcedure;
                    conexion.Open();
                    comando.Parameters.Add("@DNI", SqlDbType.NVarChar, 10).Value = dni;

                    SqlDataReader lector = comando.ExecuteReader();
                    while (lector.Read())
                    {
                        p = new Personal();
                        p.personalID = lector.GetInt16(0);
                        p.DNI = lector.GetString(1);
                        p.nombres = lector.GetString(2);
                        p.apellidos = lector.GetString(3);
                        p.direccion = lector.GetString(4);
                        p.telefono = lector.IsDBNull(5) ? null : lector.GetString(5);
                        p.eMail = lector.IsDBNull(6) ? null : lector.GetString(6);
                        lista.Add(p);
                    }
                    lector.Close();

                }
                catch (Exception)
                {
                    return null;
                }
            }
            return lista;
        }

        public List<Personal> listarPersonalByNombres(String nombres)
        {
            List<Personal> lista = new List<Personal>();
            Personal p = null;
            using (SqlConnection conexion = new SqlConnection(Conexion.Instancia.CadenaConexion()))
            using (SqlCommand comando = new SqlCommand("[proc_PersonalByNombres]", conexion))
            {
                try
                {
                    comando.CommandType = CommandType.StoredProcedure;
                    conexion.Open();
                    comando.Parameters.Add("@nombres", SqlDbType.NVarChar, 22).Value = nombres;

                    SqlDataReader lector = comando.ExecuteReader();
                    while (lector.Read())
                    {
                        p = new Personal();
                        p.personalID = lector.GetInt16(0);
                        p.DNI = lector.GetString(1);
                        p.nombres = lector.GetString(2);
                        p.apellidos = lector.GetString(3);
                        p.direccion = lector.GetString(4);
                        p.telefono = lector.IsDBNull(5) ? null : lector.GetString(5);
                        p.eMail = lector.IsDBNull(6) ? null : lector.GetString(6);
                        lista.Add(p);
                    }
                    lector.Close();

                }
                catch (Exception)
                {
                    return null;
                }
            }
            return lista;
        }

        public List<Personal> listarPersonalByApellidos(String apellidos)
        {
            List<Personal> lista = new List<Personal>();
            Personal p = null;
            using (SqlConnection conexion = new SqlConnection(Conexion.Instancia.CadenaConexion()))
            using (SqlCommand comando = new SqlCommand("[proc_PersonalByApellidos]", conexion))
            {
                try
                {
                    comando.CommandType = CommandType.StoredProcedure;
                    conexion.Open();
                    comando.Parameters.Add("@apellidos", SqlDbType.NVarChar, 22).Value = apellidos;

                    SqlDataReader lector = comando.ExecuteReader();
                    while (lector.Read())
                    {
                        p = new Personal();
                        p.personalID = lector.GetInt16(0);
                        p.DNI = lector.GetString(1);
                        p.nombres = lector.GetString(2);
                        p.apellidos = lector.GetString(3);
                        p.direccion = lector.GetString(4);
                        p.telefono = lector.IsDBNull(5) ? null : lector.GetString(5);
                        p.eMail = lector.IsDBNull(6) ? null : lector.GetString(6);
                        lista.Add(p);
                    }
                    lector.Close();

                }
                catch (Exception)
                {
                    return null;
                }
            }
            return lista;
        }
    }
}
